package jsyf.oa.web.importExcel;

import com.alibaba.dubbo.common.utils.StringUtils;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiImplicitParam;
import io.swagger.annotations.ApiOperation;
import jsyf.com.util.BaseUtils;
import jsyf.com.util.OssClient;
import jsyf.com.util.RequestUtils;
import jsyf.com.util.ResponseUtils;
import jsyf.oa.entity.apply.MoneyMainEntity;
import jsyf.oa.entity.device.DeviceServiceEntity;
import jsyf.oa.entity.report.mineCustomer.CustomerReportEntity;
import jsyf.oa.entity.salary.SalaryDetailsEntity;
import jsyf.oa.facade.attach.AttachFacade;
import jsyf.oa.facade.importExcel.ImportExcelFacade;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.http.MediaType;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.UUID;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * Created by 92852 on 2018/4/19.
 */
@Api(value = "excle导入接口", description = "excle导入接口")
@SuppressWarnings("all")
@Controller
@RequestMapping("/importExcel")
public class ImportExcelController {

    @Resource
    private ImportExcelFacade importExcelFacade;

    @Resource
    private AttachFacade attachFacade;

    public static String generateGUID(String filename) {
        String filetype = filename.substring(filename.lastIndexOf("."));
        filename = UUID.randomUUID().toString().replace("-", "");
        return filename + filetype;
    }

    HSSFFormulaEvaluator evaluator;

    @ResponseBody
    @RequestMapping(value = "/uploadFile.json", method = RequestMethod.POST)
    @ApiOperation(
            value = "文件上传及导入",
            notes = "文件上传及导入 ",
            httpMethod = "POST",
            produces = MediaType.APPLICATION_JSON_VALUE)
    @ApiImplicitParam(name = "jsonData", value = "", dataType = "String", paramType = "body")
    public void upload(HttpServletRequest request, HttpServletResponse response) {
        MultipartFile mf = RequestUtils.getUploadFile(request);
        //转换成输入流
        InputStream in = null;
        try {
            in = mf.getInputStream();
        } catch (IOException e1) {
            e1.printStackTrace();
        }
        XSSFWorkbook readWb = null;
        try {
            readWb = new XSSFWorkbook(in);/*创建excle2007*/
        } catch (IOException e) {
            e.printStackTrace();
        }
        List<DeviceServiceEntity> list = new ArrayList<>();
            /*HSSFWorkbook readWb = new HSSFWorkbook(in); 创建excle2003*/
        //遍历该表格中所有的工作表，i表示工作表的数量 getNumberOfSheets表示工作表的总数
        for (int i = 0; i < readWb.getNumberOfSheets(); i++) {
            XSSFSheet sheet = readWb.getSheetAt(i);
            // 循环行数Row
            for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {
                XSSFRow hssfRow = sheet.getRow(rowNum);
                if (hssfRow != null) {
                    XSSFCell companyName = hssfRow.getCell(0);
                    XSSFCell brandName = hssfRow.getCell(1);
                    XSSFCell deviceTypeName = hssfRow.getCell(2);
                    XSSFCell modelName = hssfRow.getCell(3);
                    XSSFCell sequence = hssfRow.getCell(4);
                    XSSFCell saledType = hssfRow.getCell(5);
                    XSSFCell years = hssfRow.getCell(6);
                    XSSFCell deliveryDate = hssfRow.getCell(7);
                    XSSFCell hours = hssfRow.getCell(8);
                    XSSFCell customerName = hssfRow.getCell(9);
                    XSSFCell customerTel = hssfRow.getCell(10);
                    XSSFCell adminName = hssfRow.getCell(11);
                    XSSFCell adminTel = hssfRow.getCell(12);
                    XSSFCell operatorName = hssfRow.getCell(13);
                    XSSFCell operatorTel = hssfRow.getCell(14);
                    /*时间格式转换*/
                    String deliveryDateString = " ";
                    String yearsDateString = " ";
                    String adminTelString = " ";
                    String customerTelString = " ";
                    String operatorTelString = " ";
                  /*  if (deliveryDate != null) {
                        if (!"不详".equals(deliveryDate.toString() ) ) {
                            SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
                            deliveryDateString = formatter.format(deliveryDate.getDateCellValue());
                        }
                    }*/

                    if (deliveryDate != null) {
                        if (deliveryDate.getCellType() == 0) {
                            if (HSSFDateUtil.isCellDateFormatted(deliveryDate)) {
                                SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
                                deliveryDateString = formatter.format(deliveryDate.getDateCellValue());
                            }
                        } else {
                            deliveryDateString = deliveryDate.getStringCellValue();
                        }
                    }
                    if (years != null) {
                        if (years.getCellType() == 0) {
                            if (HSSFDateUtil.isCellDateFormatted(years)) {
                                SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
                                yearsDateString = formatter.format(years.getDateCellValue());
                            }
                        } else {
                            yearsDateString = years.getStringCellValue();
                        }
                    }
                    if (adminTel != null) {
                        if (adminTel.getCellType() == 0) {
                            DecimalFormat df = new DecimalFormat("#");
                            adminTelString = df.format(adminTel.getNumericCellValue());
                        } else {
                            adminTelString = adminTel.toString();
                        }
                    }
                    if (customerTel != null) {
                        if (customerTel.getCellType() == 0) {
                            DecimalFormat df = new DecimalFormat("#");
                            customerTelString = df.format(customerTel.getNumericCellValue());
                        } else {
                            customerTelString = customerTel.toString();
                        }
                    }
                    if (operatorTel != null) {
                        if (operatorTel.getCellType() == 0) {
                            DecimalFormat df = new DecimalFormat("#");
                            operatorTelString = df.format(operatorTel.getNumericCellValue());
                        } else {
                            operatorTelString = operatorTel.toString();
                        }
                    }
                    DeviceServiceEntity entity = new DeviceServiceEntity();
                    if (companyName != null) {
                        entity.setCompanyName(companyName.toString());
                    }
                    if (brandName != null) {
                        entity.setBrandName(brandName.toString());
                    }
                    if (deviceTypeName != null) {
                        entity.setDeviceTypeName(deviceTypeName.toString());
                    }
                    if (modelName != null) {
                        entity.setModelName(modelName.toString());
                    }
                    if (sequence != null) {
                        entity.setSequence(sequence.toString());
                    }
                    if (saledType != null) {
                        entity.setSaledType(saledType.toString());
                    }
                    if (years != null) {
                        entity.setYears(yearsDateString.toString());
                    }
                    if (deliveryDate != null) {
                        entity.setDeliveryDate(deliveryDateString.toString());
                    }
                    if (hours != null) {
                        entity.setHours(hours.toString());
                    }
                    if (customerName != null) {
                        entity.setCustomerName(customerName.toString());
                    }
                    entity.setCustomerTel(customerTelString.toString());
                    if (adminName != null) {
                        entity.setAdminName(adminName.toString());
                    }
                    entity.setAdminTel(adminTelString.toString());
                    if (operatorName != null) {
                        entity.setOperatorName(operatorName.toString());
                    }
                    entity.setOperatorTel(operatorTelString.toString());
                    entity.setDelFlag(0);
                    list.add(entity);
                    //赋值实例对象 做插入数据库操作
                }
            }
        }
        String obj = JSON.toJSONString(list);
        String results = importExcelFacade.readExcelFile(obj);
        ResponseUtils.putJsonResponse(response, BaseUtils.succsessObjRespond(results));
    }

    @ResponseBody
    @RequestMapping(value = "/receivableFile.json", method = RequestMethod.POST)
    @ApiOperation(
            value = "导入销售合同还款计划",
            notes = "导入融资还款计划 ",
            httpMethod = "POST",
            produces = MediaType.APPLICATION_JSON_VALUE)
    @ApiImplicitParam(name = "jsonData", value = "", dataType = "String", paramType = "body")
    public void receivableFile(HttpServletRequest request, HttpServletResponse response,
                               @RequestParam String mainId) {

        MultipartFile mf = RequestUtils.getUploadFile(request);
        //转换成输入流
        InputStream in = null;
        try {
            in = mf.getInputStream();
        } catch (IOException e1) {
            e1.printStackTrace();
        }
        XSSFWorkbook readWb = null;
        try {
            readWb = new XSSFWorkbook(in);/*创建excle2007*/
        } catch (IOException e) {
            e.printStackTrace();
        }
        List<JSONObject> result = new ArrayList<>();
        for (int i = 0; i < readWb.getNumberOfSheets(); i++) {
            XSSFSheet sheet = readWb.getSheetAt(i);
            // 循环行数Row
            for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {
                XSSFRow hssfRow = sheet.getRow(rowNum);
                if (hssfRow != null) {
                    XSSFCell repaymentDate = hssfRow.getCell(0);
                    XSSFCell contractRepaymentAmount = hssfRow.getCell(1);
                    JSONObject repayment = new JSONObject();
                    if (!StringUtils.isBlank(repaymentDate.toString())) {
                        repayment.put("repaymentDate", excelTime(repaymentDate));
                    }
                    if (!StringUtils.isBlank(contractRepaymentAmount.toString())) {
                        repayment.put("contractRepaymentAmount", BigDecimal.valueOf(contractRepaymentAmount.getNumericCellValue()));
                    }
                    result.add(repayment);
                }
            }
        }
        String obj = JSON.toJSONString(result);
        String type = mainId.substring(mainId.length() - 1, mainId.length());
        mainId = mainId.substring(0, mainId.length() - 1);
        Boolean results = importExcelFacade.receivableFile(obj, mainId, type);
        if (results) {
            ResponseUtils.putJsonResponse(response, BaseUtils.succsessObjRespond(String.valueOf(results)));
        } else {
            ResponseUtils.putJsonResponse(response, BaseUtils.errorrRespond("导入数据失败"));
        }
    }

    @ResponseBody
    @RequestMapping(value = "/financingFile.json", method = RequestMethod.POST)
    @ApiOperation(
            value = "导入融资还款计划",
            notes = "导入融资还款计划 ",
            httpMethod = "POST",
            produces = MediaType.APPLICATION_JSON_VALUE)
    @ApiImplicitParam(name = "jsonData", value = "", dataType = "String", paramType = "body")
    public void financingFile(HttpServletRequest request, HttpServletResponse response,
                              @RequestParam String mainId) {

        MultipartFile mf = RequestUtils.getUploadFile(request);
        //转换成输入流
        InputStream in = null;
        try {
            in = mf.getInputStream();
        } catch (IOException e1) {
            e1.printStackTrace();
        }
        XSSFWorkbook readWb = null;
        try {
            readWb = new XSSFWorkbook(in);/*创建excle2007*/
        } catch (IOException e) {
            e.printStackTrace();
        }
        List<JSONObject> result = new ArrayList<>();
        for (int i = 0; i < readWb.getNumberOfSheets(); i++) {
            XSSFSheet sheet = readWb.getSheetAt(i);
            // 循环行数Row
            for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {
                XSSFRow hssfRow = sheet.getRow(rowNum);
                if (hssfRow != null) {
                    XSSFCell financingDate = hssfRow.getCell(0);
                    XSSFCell financingMoney = hssfRow.getCell(1);
                    JSONObject repayment = new JSONObject();
                    if (!StringUtils.isBlank(financingDate.toString())) {
                        repayment.put("financingDate", excelTime(financingDate));
                    }
                    if (!StringUtils.isBlank(financingMoney.toString())) {
                        repayment.put("financingMoney", BigDecimal.valueOf(financingMoney.getNumericCellValue()));
                    }
                    result.add(repayment);
                }
            }
        }
        String obj = JSON.toJSONString(result);
        String type = mainId.substring(mainId.length() - 1, mainId.length());
        mainId = mainId.substring(0, mainId.length() - 1);
        Boolean results = importExcelFacade.financingFile(obj, mainId, type);
        if (results) {
            ResponseUtils.putJsonResponse(response, BaseUtils.succsessObjRespond(String.valueOf(results)));
        } else {
            ResponseUtils.putJsonResponse(response, BaseUtils.errorrRespond("导入数据失败"));
        }
    }


    @ResponseBody
    @RequestMapping(value = "/availableFile.json", method = RequestMethod.POST)
    @ApiOperation(
            value = "导入还款录入",
            notes = "导入还款录入 ",
            httpMethod = "POST",
            produces = MediaType.APPLICATION_JSON_VALUE)
    @ApiImplicitParam(name = "jsonData", value = "", dataType = "String", paramType = "body")
    public void availableFile(HttpServletRequest request, HttpServletResponse response,
                              @RequestParam String mainId) {

        MultipartFile mf = RequestUtils.getUploadFile(request);
        //转换成输入流
        InputStream in = null;
        try {
            in = mf.getInputStream();
        } catch (IOException e1) {
            e1.printStackTrace();
        }
        XSSFWorkbook readWb = null;
        try {
            readWb = new XSSFWorkbook(in);/*创建excle2007*/
        } catch (IOException e) {
            e.printStackTrace();
        }
        List<JSONObject> result = new ArrayList<>();
        for (int i = 0; i < readWb.getNumberOfSheets(); i++) {
            XSSFSheet sheet = readWb.getSheetAt(i);
            // 循环行数Row
            for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {
                XSSFRow hssfRow = sheet.getRow(rowNum);
                if (hssfRow != null) {
                    XSSFCell availableDate = hssfRow.getCell(0);
                    XSSFCell availableType = hssfRow.getCell(1);
                    XSSFCell availableMoney = hssfRow.getCell(2);
                    XSSFCell remark = hssfRow.getCell(3);
                    JSONObject repayment = new JSONObject();
                    if (availableDate != null || !availableDate.equals("")) {
                        repayment.put("availableDate", excelTime(availableDate));
                    }
                    if (availableType != null || !availableType.equals("")) {
                        repayment.put("availableType", availableType.toString());
                    }
                    if (availableMoney != null || !availableMoney.equals("")) {
                        repayment.put("availableMoney", BigDecimal.valueOf(availableMoney.getNumericCellValue()));
                    }
                    if (remark != null || !remark.equals("")) {
                        repayment.put("remark", remark.toString());
                    }
                    result.add(repayment);
                }
            }
        }
        String obj = JSON.toJSONString(result);
        Boolean results = importExcelFacade.availableFile(obj, mainId);
        if (results) {
            ResponseUtils.putJsonResponse(response, BaseUtils.succsessObjRespond(String.valueOf(results)));
        } else {
            ResponseUtils.putJsonResponse(response, BaseUtils.errorrRespond("导入数据失败"));
        }
    }

    @ResponseBody
    @RequestMapping(value = "/customerFile.json", method = RequestMethod.POST)
    @ApiOperation(
            value = "导入客户信息",
            notes = "导入客户信息 ",
            httpMethod = "POST",
            produces = MediaType.APPLICATION_JSON_VALUE)
    @ApiImplicitParam(name = "jsonData", value = "", dataType = "String", paramType = "body")
    public void customerFile(HttpServletRequest request, HttpServletResponse response,
                             @RequestParam String createUserId) {
        MultipartFile mf = RequestUtils.getUploadFile(request);
        //转换成输入流
        InputStream in = null;
        try {
            in = mf.getInputStream();
        } catch (IOException e1) {
            e1.printStackTrace();
        }
        XSSFWorkbook readWb = null;
        try {
            readWb = new XSSFWorkbook(in);/*创建excle2007*/
        } catch (IOException e) {
            e.printStackTrace();
        }
        List<JSONObject> result = new ArrayList<>();
        List<CustomerReportEntity> list = new ArrayList<>();
        String createName = "";
        String createTel = "";
        for (int i = 0; i < readWb.getNumberOfSheets(); i++) {
            XSSFSheet sheet = readWb.getSheetAt(i);
            // 循环行数Row
            for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {
                XSSFRow hssfRow = sheet.getRow(rowNum);
                if (hssfRow != null) {
                    XSSFCell createUserName = hssfRow.getCell(0);
                    XSSFCell createUserTel = hssfRow.getCell(1);
                    XSSFCell customerName = hssfRow.getCell(2);
                    XSSFCell tel = hssfRow.getCell(3);
                    XSSFCell customerType = hssfRow.getCell(4);
                    XSSFCell customerCredit = hssfRow.getCell(5);
                    XSSFCell birthday = hssfRow.getCell(6);
                    XSSFCell wechat = hssfRow.getCell(7);
                    XSSFCell cityName = hssfRow.getCell(8);
                    XSSFCell countyName = hssfRow.getCell(9);
                    XSSFCell existingDevice = hssfRow.getCell(10);

                    String telString = " ";
                    String birthdayString = "";
                    String wechatString = "";

                    if (createUserName != null) {
                        createName = createUserName.toString();
                    }

                    if (createUserTel != null) {
                        if (createUserTel.getCellType() == 0) {
                            DecimalFormat df = new DecimalFormat("#");
                            createTel = df.format(createUserTel.getNumericCellValue());
                        } else {
                            createTel = createUserTel.toString();
                        }
                    }
                    if (tel != null) {
                        if (tel.getCellType() == 0) {
                            DecimalFormat df = new DecimalFormat("#");
                            telString = df.format(tel.getNumericCellValue());
                        } else {
                            telString = tel.toString();
                        }
                    }
                    if (wechat != null) {
                        if (wechat.getCellType() == 0) {
                            DecimalFormat df = new DecimalFormat("#");
                            wechatString = df.format(wechat.getNumericCellValue());
                        } else {
                            wechatString = wechat.toString();
                        }
                    }
                    if (birthday != null) {
                        if (birthday.getCellType() == 0) {
                            if (HSSFDateUtil.isCellDateFormatted(birthday)) {
                                SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
                                birthdayString = formatter.format(birthday.getDateCellValue());
                            }
                        } else {
                            birthdayString = birthday.getStringCellValue();
                        }
                    }

                    CustomerReportEntity entity = new CustomerReportEntity();
                    if (customerName != null) {
                        entity.setName(customerName.toString());
                    }

                    if (tel != null) {
                        entity.setTel(telString.toString());
                    }

                    if (customerType != null) {
                        entity.setCustomerType(customerType.toString());
                    }

                    if (customerCredit != null) {
                        entity.setCustomerCredit(customerCredit.toString());
                    }

                    if (birthday != null) {
                        entity.setBirthday(birthdayString.toString());
                    }

                    if (wechat != null) {
                        entity.setWechat(wechatString.toString());
                    }

                    if (cityName != null) {
                        entity.setCityName(cityName.toString());
                    }

                    if (countyName != null) {
                        entity.setCountyName(countyName.toString());
                    }

                    if (existingDevice != null) {
                        entity.setExistingDevice(existingDevice.toString().toUpperCase());//转换为大写
                    }
                    list.add(entity);
                    //赋值实例对象 做插入数据库操作
                }
            }
        }
        String obj = JSON.toJSONString(list);
        String results = importExcelFacade.readExcelCustomer(obj, createName, createTel);
        ResponseUtils.putJsonResponse(response, BaseUtils.succsessObjRespond(results));
    }

    @ResponseBody
    @RequestMapping(value = "/salaryFile.json", method = RequestMethod.POST)
    @ApiOperation(
            value = "导入工资条信息",
            notes = "导入工资条信息 ",
            httpMethod = "POST",
            produces = MediaType.APPLICATION_JSON_VALUE)
    @ApiImplicitParam(name = "jsonData", value = "", dataType = "String", paramType = "body")
    public void salaryFile(HttpServletRequest request, HttpServletResponse response) {
        String filename = null;
        try {
            MultipartFile mf = RequestUtils.getUploadFile(request);
            if (mf == null || mf.isEmpty()) {
                ResponseUtils.putJsonFailureResponse(response, "未指定上传文件");
                return;
            }
            filename = generateGUID(mf.getOriginalFilename());
            InputStream inputStream = mf.getInputStream();
            String fjurl = OssClient.uploadFileToOSS(inputStream, filename);
            //往数据库中存数据
            String fjname = mf.getOriginalFilename();
            String extension = getExtName(mf.getOriginalFilename());
            String size = String.valueOf(mf.getSize());

            String fjid = attachFacade.save(fjurl, fjname, extension, size);
            String regex = "\\d{4}年\\d{1,2}";
            Pattern pattern = Pattern.compile(regex);
            Matcher matcher = pattern.matcher(fjname);
            String month = "";
            if (matcher.find()) {
                month = matcher.group(0).replace("年", "-");
            }
            if (fjname.indexOf("工资表") > -1) {
                //转换成输入流
                InputStream in = null;
                try {
                    in = mf.getInputStream();
                } catch (IOException e1) {
                    e1.printStackTrace();
                }
                XSSFWorkbook readWb = null;
                try {
                    readWb = new XSSFWorkbook(in);/*创建excle2007*/
                } catch (IOException e) {
                    e.printStackTrace();
                }
                List<JSONObject> result = new ArrayList<>();
                List<SalaryDetailsEntity> list = new ArrayList<>();
                String createName = "";
                String createTel = "";
                for (int i = 0; i < readWb.getNumberOfSheets(); i++) {
                    if (readWb.getSheetName(i).equals("工资")) {
                        XSSFSheet sheet = readWb.getSheetAt(i);
                        // 循环行数Row
                        for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {
                            XSSFRow hssfRow = sheet.getRow(rowNum);
                            if (!StringUtils.isBlank(hssfRow.toString()) && hssfRow.getCell(0) != null) {
                                String ryNo = getCellValue(hssfRow.getCell(0));//编号
                                String company = getCellValue(hssfRow.getCell(1));         //公司                                                       //公司
                                String departName = getCellValue(hssfRow.getCell(2));//部门
                                String userName = getCellValue(hssfRow.getCell(3));//姓名
                                String roleName = getCellValue(hssfRow.getCell(4));//岗位
                                String entryDate = excelTime(hssfRow.getCell(5));//入职日期
                                String preSalaryStandard = getCellValue(hssfRow.getCell(6));//上月工资标准
                                String curSalaryStandard = getCellValue(hssfRow.getCell(7));//本月工资标准
                                String shouldSignIn = getCellValue(hssfRow.getCell(8));//应出勤
                                String realSignIn = getCellValue(hssfRow.getCell(9));//实际出勤
                                String leaveDays = getCellValue(hssfRow.getCell(10));//请假天数
                                String signInStandard = getCellValue(hssfRow.getCell(11));//本月工资标准出勤
                                String signInFormer = getCellValue(hssfRow.getCell(12));//原工资标准出勤
                                String overtimeDays = getCellValue(hssfRow.getCell(13));//加班天数
                                String wages = getCellValue(hssfRow.getCell(14));//出勤工资
                                String foodAllowance = getCellValue(hssfRow.getCell(15));//餐补
                                String carSupplementStandard = getCellValue(hssfRow.getCell(16));//车补标准
                                String carSupplement = getCellValue(hssfRow.getCell(17));//车补
                                String callSupplement = getCellValue(hssfRow.getCell(18));//话费补助
                                String houseSupplement = getCellValue(hssfRow.getCell(19));//住房补助
                                String otherSupplement = getCellValue(hssfRow.getCell(20));//其他补助
                                String bouns = getCellValue(hssfRow.getCell(21));//奖金
                                String salesAward = getCellValue(hssfRow.getCell(22));//奖金//销售绩效考核奖励
                                String salesPunishment = getCellValue(hssfRow.getCell(23));//奖金//销售绩效考核惩罚
                                String personnelDeduction = getCellValue(hssfRow.getCell(24));//团队组建扣款
                                String otherDetuction = getCellValue(hssfRow.getCell(25));//其他扣款
                                String payable = getCellValue(hssfRow.getCell(26));//应发工资
                                String insuranceGold = getCellValue(hssfRow.getCell(27));//五险一金
                                String taxAmount = getCellValue(hssfRow.getCell(28));//计税金额
                                String shouldTax = getCellValue(hssfRow.getCell(29));//应扣个税
                                String repayment = getCellValue(hssfRow.getCell(30));//还个人借款
                                String realPay = getCellValue(hssfRow.getCell(31));//实发金额
                                String remark = getCellValue(hssfRow.getCell(32));//备注
                                String wegeCard = getCellValue(hssfRow.getCell(33));//卡号
                                String province = getCellValue(hssfRow.getCell(34));//省份
                                String city = getCellValue(hssfRow.getCell(35));//县市
                                String bank = getCellValue(hssfRow.getCell(36));//开户行
                                String card = getCellValue(hssfRow.getCell(37));//身份证号码
                                SalaryDetailsEntity entity = new SalaryDetailsEntity();
                                if (StringUtils.isNotEmpty(ryNo)) {
                                    entity.setRyNo(ryNo.toString());
                                }
                                if (StringUtils.isNotEmpty(company)) {
                                    entity.setCompanyName(company.toString());
                                }
                                if (StringUtils.isNotEmpty(departName)) {
                                    entity.setDepartName(departName.toString());
                                }//1.部门
                                if (StringUtils.isNotEmpty(userName)) {
                                    entity.setUserName(userName.toString());
                                }//2.姓名
                                if (StringUtils.isNotEmpty(roleName)) {
                                    entity.setRoleName(roleName.toString());
                                }//3.职务
                                if (StringUtils.isNotEmpty(entryDate)) {
                                    entity.setEntryDate(entryDate.toString());
                                }//4.入职日期
                                if (StringUtils.isNotEmpty(preSalaryStandard)) {
                                    entity.setPreSalaryStandard(formateRate(preSalaryStandard));
                                }//5.上月工资标准
                                if (StringUtils.isNotEmpty(curSalaryStandard)) {
                                    entity.setCurSalaryStandard(formateRate(curSalaryStandard));
                                }//6.本月工资标准
                                if (StringUtils.isNotEmpty(shouldSignIn)) {
                                    entity.setShouldSignIn(shouldSignIn.toString());
                                }//7.应出勤
                                if (StringUtils.isNotEmpty(realSignIn)) {
                                    entity.setRealSignIn(realSignIn.toString());
                                }//8.实际出勤
                                if (StringUtils.isNotEmpty(leaveDays)) {
                                    entity.setLeaveDays(leaveDays.toString());
                                }//9.请假天数
                                if (StringUtils.isNotEmpty(signInStandard)) {
                                    entity.setSignInStandard(signInStandard.toString());
                                }//10.本月工资标准出勤
                                if (StringUtils.isNotEmpty(signInFormer)) {
                                    entity.setSignInFormer(signInFormer.toString());
                                }//11.原工资标准出勤
                                if (StringUtils.isNotEmpty(overtimeDays)) {
                                    entity.setOvertimeDays(overtimeDays.toString());
                                }//12.加班天数
                                if (StringUtils.isNotEmpty(wages)) {
                                    entity.setWages(formateRate(wages));
                                }//13.出勤工资
                                if (StringUtils.isNotEmpty(foodAllowance)) {
                                    entity.setFoodAllowance(formateRate(foodAllowance));
                                }//14.餐补
                                if (StringUtils.isNotEmpty(carSupplementStandard)) {
                                    entity.setCarSupplementStandard(formateRate(carSupplementStandard));
                                }//15.车补标准
                                if (StringUtils.isNotEmpty(carSupplement)) {
                                    entity.setCarSupplement(formateRate(carSupplement));
                                }//16.车补
                                if (StringUtils.isNotEmpty(callSupplement)) {
                                    entity.setCallSupplement(formateRate(callSupplement));
                                }//17.话费补助
                                if (StringUtils.isNotEmpty(houseSupplement)) {
                                    entity.setHouseSupplement(formateRate(houseSupplement));
                                }//18.住房补助
                                if (StringUtils.isNotEmpty(otherSupplement)) {
                                    entity.setOtherSupplement(formateRate(otherSupplement));
                                }//19.其他补助
                                if (StringUtils.isNotEmpty(personnelDeduction)) {
                                    entity.setPersonnelDeduction(formateRate(personnelDeduction));
                                }//20.人员扣款
                                if (StringUtils.isNotEmpty(otherDetuction)) {
                                    entity.setOtherDetuction(formateRate(otherDetuction));
                                }
                                if (StringUtils.isNotEmpty(bouns)) {
                                    entity.setBouns(formateRate(bouns));
                                }
                                ;//奖金
                                if (StringUtils.isNotEmpty(salesAward)) {
                                    entity.setSalesAward(formateRate(salesAward));
                                }
                                ;//销售绩效考核奖励
                                if (StringUtils.isNotEmpty(salesPunishment)) {
                                    entity.setSalesPunishment(formateRate(salesPunishment));
                                }
                                ;//销售绩效考核

                                //21.其他扣款
                                if (StringUtils.isNotEmpty(payable)) {
                                    entity.setPayable(formateRate(payable));
                                }//22.应发工资
                                if (StringUtils.isNotEmpty(insuranceGold)) {
                                    entity.setInsuranceGold(formateRate(insuranceGold));
                                }//23.五险一金
                                if (StringUtils.isNotEmpty(taxAmount)) {
                                    entity.setTaxAmount(formateRate(taxAmount));
                                }//24.计税金额
                                if (StringUtils.isNotEmpty(shouldTax)) {
                                    entity.setShouldTax(formateRate(shouldTax));
                                }//25.应扣个税
                                if (StringUtils.isNotEmpty(repayment)) {
                                    entity.setRepayment(formateRate(repayment));
                                }//26.还个人借款
                                if (StringUtils.isNotEmpty(realPay)) {
                                    entity.setRealPay(formateRate(realPay));
                                }//27.实发金额
                                if (StringUtils.isNotEmpty(wegeCard)) {
                                    entity.setWegeCard(wegeCard.toString());
                                }//28.卡号
                                if (StringUtils.isNotEmpty(province)) {
                                    entity.setProvince(province.toString());
                                }//29.省份
                                if (StringUtils.isNotEmpty(city)) {
                                    entity.setCity(city.toString());
                                }//30.县市
                                if (StringUtils.isNotEmpty(bank)) {
                                    entity.setBank(bank.toString());
                                }//31.开户行
                                if (StringUtils.isNotEmpty(card)) {
                                    entity.setCard(card.toString());
                                }//32.身份证号码
                                if (StringUtils.isNotEmpty(remark)) {
                                    entity.setRemark(remark.toString());
                                }//33.备注
                                if (StringUtils.isNotEmpty(month)) {
                                    entity.setMonth(month.toString());
                                }//35.月份
                                list.add(entity);
                                //赋值实例对象 做插入数据库操作
                            }
                        }
                    }
                }
                String obj = JSON.toJSONString(list);
                String results = importExcelFacade.readExcelSalary(obj, month, fjname);
            }
            //返回数据给前台
            JSONObject objResult = new JSONObject();
            objResult.put("id", fjid);
            objResult.put("name", fjname);
            objResult.put("url", fjurl);
            String record = JSON.toJSONString(objResult);
            ResponseUtils.putJsonResponse(response, record);


        } catch (Exception e) {
            e.printStackTrace();
            ResponseUtils.putTextResponse(response, "文件上传出错！");
        }

    }

    public String formateRate(String rateStr){
        if(rateStr.indexOf(".") != -1){
            //获取小数点的位置
            int num = 0;
            num = rateStr.indexOf(".");

            //获取小数点后面的数字 是否有两位 不足两位补足两位
            String dianAfter = rateStr.substring(0,num+1);
            String afterData = rateStr.replace(dianAfter, "");
            if(afterData.length() < 2){
                afterData = afterData + "0" ;
            }else{
                afterData = afterData;
            }
            return rateStr.substring(0,num) + "." + afterData.substring(0,2);
        }else{
            if(rateStr == "1"){
                return "100";
            }else{
                return rateStr;
            }
        }
    }

    public static String getCellValue(Cell cell) {
        int cellType = cell.getCellType();
        String cellValue = "";
        switch (cellType) {
            case HSSFCell.CELL_TYPE_NUMERIC:
                cellValue = String.valueOf(cell.getNumericCellValue());
                break;

            case HSSFCell.CELL_TYPE_FORMULA:
                try {
                    cellValue = cell.getStringCellValue();
                } catch (IllegalStateException e) {
                    cellValue = String.valueOf(cell.getNumericCellValue());
                }
                break;

            default:
                cellValue = cell.getStringCellValue();
        }

        return cellValue.trim();

    }

    /**
     * @param cell import org.apache.poi.ss.usermodel.Cell;
     *             关于如何将Excel表格中的时间字符串的数字格式  转换成 格式化的时间字符串
     * @return
     * @author SHUN
     */
    public String excelTime(Cell cell) {
        String guarantee_time = null;
        if (DateUtil.isCellDateFormatted(cell)) {
            //用于转化为日期格式
            Date d = cell.getDateCellValue();
//	             System.err.println(d.toString());
//	             DateFormat formater = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            DateFormat formater = new SimpleDateFormat("yyyy-MM-dd");
            if (d == null) {
                guarantee_time = "";
            } else {
                guarantee_time = formater.format(d);
            }
        }
        return guarantee_time;
    }


    @ResponseBody
    @RequestMapping(value = "/uploadMoney.json", method = RequestMethod.POST)
    @ApiOperation(
            value = "文件上传及导入",
            notes = "文件上传及导入 ",
            httpMethod = "POST",
            produces = MediaType.APPLICATION_JSON_VALUE)
    @ApiImplicitParam(name = "jsonData", value = "", dataType = "String", paramType = "body")
    public void uploadMoney(HttpServletRequest request, HttpServletResponse response) {
        MultipartFile mf = RequestUtils.getUploadFile(request);
        //转换成输入流
        InputStream in = null;
        try {
            in = mf.getInputStream();
        } catch (IOException e1) {
            e1.printStackTrace();
        }
        XSSFWorkbook readWb = null;
        try {
            readWb = new XSSFWorkbook(in);/*创建excle2007*/
        } catch (IOException e) {
            e.printStackTrace();
        }
        List<MoneyMainEntity> list = new ArrayList<>();
            /*HSSFWorkbook readWb = new HSSFWorkbook(in); 创建excle2003*/
        //遍历该表格中所有的工作表，i表示工作表的数量 getNumberOfSheets表示工作表的总数
        for (int i = 0; i < readWb.getNumberOfSheets(); i++) {
            XSSFSheet sheet = readWb.getSheetAt(i);
            // 循环行数Row
            for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {
                XSSFRow hssfRow = sheet.getRow(rowNum);
                if (hssfRow != null) {
                    XSSFCell type = hssfRow.getCell(0);
                    XSSFCell title = hssfRow.getCell(1);
                    XSSFCell seriesNumber = hssfRow.getCell(2);
                    XSSFCell orgName = hssfRow.getCell(3);
                    XSSFCell regionName = hssfRow.getCell(4);
                    XSSFCell description = hssfRow.getCell(5);
                    XSSFCell createUserId = hssfRow.getCell(6);
                    XSSFCell createTime = hssfRow.getCell(7);
                    XSSFCell payUserId = hssfRow.getCell(8);
                    XSSFCell payDate = hssfRow.getCell(9);
                    XSSFCell remark = hssfRow.getCell(10);
                    XSSFCell urgentFlag = hssfRow.getCell(11);
                    XSSFCell auditFlag = hssfRow.getCell(12);
                    XSSFCell payRemark = hssfRow.getCell(13);
                    XSSFCell money = hssfRow.getCell(14);
                    XSSFCell createUserName = hssfRow.getCell(15);
                    XSSFCell currentAuditorId = hssfRow.getCell(16);
                    XSSFCell currentAuditorName = hssfRow.getCell(17);
                    XSSFCell companyId = hssfRow.getCell(18);
                    XSSFCell payUserName = hssfRow.getCell(19);
                    XSSFCell createUserTel = hssfRow.getCell(20);
                    XSSFCell payUserTel = hssfRow.getCell(21);
                    XSSFCell copierReturnReason = hssfRow.getCell(22);
                    XSSFCell payReturnUserId = hssfRow.getCell(23);
                    XSSFCell payReturnUserName = hssfRow.getCell(24);
                    XSSFCell payReturnReasonType = hssfRow.getCell(25);
                    XSSFCell payReturnReasonDesc = hssfRow.getCell(26);
                    XSSFCell returnType = hssfRow.getCell(27);
                    MoneyMainEntity entity = new MoneyMainEntity();
                    entity.setType(type.toString());
                   /* entity.setTitle(title.toString());
                    entity.setSeriesNumber(seriesNumber.toString());
                    entity.setOrgName(orgName.toString());
                    entity.setRegionName(regionName.toString());
                    entity.setDescription(description.toString());
                    entity.setPayUserId(payUserId.toString());
                    entity.setPayDate(payDate.toString());
                    entity.setRemark(remark.toString());
                    entity.setUrgentFlag(urgentFlag.toString());
                    entity.setAuditFlag(auditFlag.toString());*/
                    //BigDecimal bd=new BigDecimal(money.toString());
                   /* entity.setPayRemark(payRemark.toString());
                    //entity.setMoney(bd);
                    entity.setCreateUserName(createUserName.toString());
                    entity.setCreateUserId(createUserId.toString());
                    entity.setCurrentAuditorId(currentAuditorId.toString());
                    entity.setCurrentAuditorName(currentAuditorName.toString());
                    entity.setCompanyId(companyId.toString());
                    entity.setPayUserName(payUserName.toString());
                    entity.setCopierReturnReason(copierReturnReason.toString());
                    entity.setPayReturnUserId(payReturnUserId.toString());
                    entity.setPayReturnUserName(payReturnUserName.toString());
                    entity.setPayReturnReasonType(payReturnReasonType.toString());
                    entity.setPayReturnReasonDesc(payReturnReasonDesc.toString());
                    entity.setReturnType(returnType.toString());*/
                    list.add(entity);
                    //赋值实例对象 做插入数据库操作
                }
            }
        }
        String obj = JSON.toJSONString(list);
        String results = importExcelFacade.readExcelMoney(obj);
        ResponseUtils.putJsonResponse(response, BaseUtils.succsessObjRespond(results));
    }

    //获取后缀名
    public String getExtName(String filename) {
        if (org.apache.commons.lang3.StringUtils.isBlank(filename)) {
            return null;
        }
        int i = filename.indexOf(".");
        if (i >= 0) {
            return filename.substring(i + 1).trim().toLowerCase();
        }
        return null;
    }
}
